package mgr;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import bean.MemberBean;
import bean.StdBean;

public class MemberMgr {
	Connection conn = null;
	
	public MemberMgr(){
		DBConnect db = new DBConnect();
		conn = db.getConnection();
	}
	
	public boolean loginCheck(String id, String pw) {
		boolean flag = false;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from member "
				+ "where id = '"+id+"' and password = '"+pw+"'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				if(rs.getInt(1)>0){
					flag = true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {rs.close();} catch (SQLException e){}
			try {stmt.close();} catch (SQLException e){}
			try {conn.close();} catch (SQLException e){}
		}
		return flag;
	}

	public MemberBean logCheck(MemberBean bean) {
		bean = new MemberBean();
		String id = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from member "
				+ "where id = '"+id+"'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean.setId(rs.getString(1));
				bean.setPassword(rs.getString(2));
				bean.setName(rs.getString(3));
				bean.setAge(rs.getInt(4));
				bean.setGender(rs.getString(5));
				bean.setEmail(rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {rs.close();} catch (SQLException e){}
			try {stmt.close();} catch (SQLException e){}
			try {conn.close();} catch (SQLException e){}
		}
		return bean;
	}

	public MemberBean getMember(String id) {
		MemberBean bean = new MemberBean();
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select * from member where id = '"+id+"'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean.setId(rs.getString(1));
				bean.setPassword(rs.getString(2));
				bean.setName(rs.getString(3));
				bean.setAge(rs.getInt(4));
				bean.setGender(rs.getString(5));
				bean.setEmail(rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {rs.close();} catch (SQLException e){}
			try {stmt.close();} catch (SQLException e){}
			try {conn.close();} catch (SQLException e){}
		}
		return bean;
	}
	
	public boolean memUpdate(MemberBean bean) {
		boolean flag = false;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "update member "
				+ "set password=?, name=?, age=?, gender=?, email=? "
				+ "where id=?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(2, bean.getPassword());
			pstmt.setString(3, bean.getName());
			pstmt.setInt(4, bean.getAge());
			pstmt.setString(5, bean.getGender());
			pstmt.setString(6, bean.getEmail());
			pstmt.setString(1, bean.getId());
			int sel = pstmt.executeUpdate();
				if(sel > 0){
					flag = true;
				}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {rs.close();} catch (SQLException e){}
			try {pstmt.close();} catch (SQLException e){}
			try {conn.close();} catch (SQLException e){}
		}
		return flag;
	}
	
	public ArrayList<StdBean> stdlist() {
		ArrayList<StdBean> list = new ArrayList<StdBean>();
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select * from student";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				StdBean bean = new StdBean();
				bean.setStdno(rs.getString(1));
				bean.setStdname(rs.getString(2));
				bean.setStdmajor(rs.getString(3));
				bean.setStdphone(rs.getString(4));
				bean.setStdemail(rs.getString(5));
				list.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {rs.close();} catch (SQLException e){}
			try {stmt.close();} catch (SQLException e){}
			try {conn.close();} catch (SQLException e){}
		}
		return list;
	}
	
	public boolean deleteMem(String stdno) {
		boolean flag = false;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				StdBean bean = new StdBean();
				bean.setStdno(rs.getString(1));
				bean.setStdname(rs.getString(2));
				bean.setStdmajor(rs.getString(3));
				bean.setStdphone(rs.getString(4));
				bean.setStdemail(rs.getString(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {rs.close();} catch (SQLException e){}
			try {stmt.close();} catch (SQLException e){}
			try {conn.close();} catch (SQLException e){}
		}
		return flag;
	}
	
	public ArrayList<MemberBean> getlist() {
		ArrayList<MemberBean> list = new ArrayList<MemberBean>();
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select * from member";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				MemberBean bean = new MemberBean();
				bean.setId(rs.getString(1));
				bean.setPassword(rs.getString(2));
				bean.setName(rs.getString(3));
				bean.setAge(rs.getInt(4));
				bean.setGender(rs.getString(5));
				bean.setEmail(rs.getString(6));
				list.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {rs.close();} catch (SQLException e){}
			try {stmt.close();} catch (SQLException e){}
			try {conn.close();} catch (SQLException e){}
		}
		return list;
	}
	
	public ArrayList<StdBean> stulist() {
		ArrayList<StdBean> list = new ArrayList<StdBean>();
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select stdname from student";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				StdBean bean = new StdBean();
				bean.setStdname(rs.getString(1));
				list.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {rs.close();} catch (SQLException e){}
			try {stmt.close();} catch (SQLException e){}
			try {conn.close();} catch (SQLException e){}
		}
		return list;
	}
}
